--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_CPT_PLANCOMPTABLE AS (SELECT * FROM &erp.CPT_PLANCOMPTABLE WHERE 1=2);




DELETE FROM se_CPT_PLANCOMPTABLE;

 INSERT INTO se_CPT_PLANCOMPTABLE
 (SELECT '01' soc_cod,
         '01' ets_cod,
         id || '000000' cptcompte_no,
         NULL postana_cod,
         NULL destana_cod,
         0 enreg_id,
         'N' enreg_s,
         libelle cptcompte_lib,
         NULL cptcompte_lib2
    FROM uv_mgeneraux
    WHERE Length(id)=6) ;

 UPDATE se_CPT_PLANCOMPTABLE tf SET enreg_id=(select enreg_id FROM &erp.CPT_PLANCOMPTABLE f WHERE f.soc_cod=tf.soc_cod AND f.ets_cod=tf.ets_cod AND f.cptcompte_no=tf.cptcompte_no  ) ;
 UPDATE se_CPT_PLANCOMPTABLE tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('soc_cod@ets_cod@cptcompte_no','CPT_PLANCOMPTABLE','O') ;

 commit;



select * from (
SELECT &outils.stragg(source) source,soc_cod, ets_cod, cptcompte_no, postana_cod, destana_cod, enreg_id, enreg_s, cptcompte_lib, cptcompte_lib2
FROM (
  SELECT 'uv' source ,g.* FROM se_CPT_PLANCOMPTABLE g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.CPT_PLANCOMPTABLE g
  )
GROUP BY soc_cod, ets_cod, cptcompte_no, postana_cod, destana_cod, enreg_id, enreg_s, cptcompte_lib, cptcompte_lib2
HAVING Count(1)!=2) r
ORDER BY soc_cod, ets_cod, cptcompte_no;